# -*- coding: utf-8 -*-
from openerp import tools
from openerp.osv import fields, osv


class purchase_arrival_rate(osv.osv):
    # 采购单产品毛利统计
    _name = 'purchase.arrival.rate'
    _description = u'采购到货率'
    _auto = False

    _columns = {
        'month': fields.char(string=u'月份', readonly=True),
        'arrival_rate': fields.float(u'产品条数到货率（百分比）%', readonly=True),
        'name': fields.char(string=u'姓名', readonly=True)
    }

    # 建立视图
    def init(self, cr):
        tools.drop_view_if_exists(cr, self._table)
        cr.execute('''CREATE OR REPLACE VIEW %s AS (
    SELECT
      ss.id AS id,
      ss.month AS month,
      ss.name AS name,
      round((ss.product_num - ss.delay_change_num) :: FLOAT / ss.product_num :: FLOAT * 100)  AS arrival_rate
    FROM (
           SELECT
             min(s.id) AS id,
             to_char(s.date_order, 'YYYY-MM') AS month,
             s.login                          AS name,
             sum(CASE WHEN is_delay OR modify_count > 0
               THEN 1
                 ELSE 0 END)                     delay_change_num,
             count(*)                         AS product_num
           FROM (
                  SELECT
                    min(pol.id) AS id,
                    pol.id                        AS pol_id,
                    ru.login,
                    po.date_order,
                    pol.expected_delivery_time,
                    max(sm.date)                  AS date_done,
                    (SELECT count(*)
                     FROM value_history vh
                     WHERE
                       model_name = 'purchase.order.line' AND to_date(old_val, 'YYYY-MM-DD') < date(create_date + '8 Hours')
                       AND vh.record_id = pol.id) AS modify_count,
                    CASE
                    WHEN max(sm.date) IS NULL
                      THEN DATE(now()) > pol.expected_delivery_time
                    ELSE DATE(max(sm.date)) > pol.expected_delivery_time
                    END                              is_delay

                  FROM purchase_order po
                    INNER JOIN purchase_order_line pol ON pol.order_id = po.id
                    LEFT JOIN stock_move sm ON sm.po_id = po.id AND sm.product_id = pol.product_id
                    LEFT JOIN stock_picking sp ON sp.id = sm.picking_id AND sp.picking_type_id = 1
                    LEFT JOIN res_users ru ON ru.id = po.create_uid
                  WHERE po.state NOT IN ('cancel', 'draft') AND po.create_uid NOT IN (1, 5)
                  GROUP BY pol.id, ru.login, po.date_order, pol.expected_delivery_time
                ) s
           GROUP BY to_char(s.date_order, 'YYYY-MM'), s.login) ss);''' % self._table)





